package com.tl.spider.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.List;

import com.tl.spider.config.Config;
import com.tl.spider.pojo.ParserPojo;
import com.tl.spider.service.statistic.StatisticManager;
import com.tl.spider.utils.common_utils.DBPoolUtils;

/**
 * 新闻数据持久化
 * 
 * @date 2021年7月20日
 * @author Antg
 */
public class NewsDataDao {
	/**
	 * 将解析得到的结果全部持久化到数据库中
	 * 
	 * @param list
	 * @date 2021年7月20日
	 * @author Antg
	 */
	public static void insert(List<ParserPojo> list) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			// 获取数据库连接
			conn = DBPoolUtils.getInstance().getBasicDataSource()
					.getConnection();
			// 创建sql语句
			String sql = "insert into news_data(title,pub_time,create_time,from_url_id) values(?,?,?,?)";
			// 获取会话
			ps = conn.prepareStatement(sql);
			// 遍历,批量插入数据
			for (ParserPojo ele : list) {
				ps.setString(1, ele.getTitle());
				ps.setString(2, ele.getPubTime());
				ps.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
				String url = ele.getUrlTaskPojo().getUrl();
				int fromId = searchUrlId(url);
				if (fromId != 0) {
					ps.setInt(4, fromId);
				} else {
					insertUrl(url);
					fromId = searchUrlId(url);
					ps.setInt(4, fromId);
				}
				ps.addBatch();
				// 统计
				StatisticManager.allNumCounter();
				StatisticManager.dayNumCounter();
			}
			int result[] = ps.executeBatch();
			System.out.println("本页插入数据条目------>" + result.length);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBPoolUtils.close(conn, ps);
		}
	}

	/**
	 * 将解析得到的结果持久化到数据库中(带有判重效果)
	 * 
	 * @param list
	 * @date 2021年7月20日
	 * @author Antg
	 */
	public static void insertWithUniq(List<ParserPojo> list) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			// 获取数据库连接
			conn = DBPoolUtils.getInstance().getBasicDataSource()
					.getConnection();
			// 创建sql语句
			String sql = "insert into news_data(title,pub_time,create_time,from_url_id) values(?,?,?,?)";
			// 获取会话
			ps = conn.prepareStatement(sql);
			// 遍历,批量插入数据
			int counterUniq = 0;
			int counterInsert = 0;// 插入条数
			for (ParserPojo ele : list) {
				// 进行判断,若果前n条一样说明该都是重复的
				if (StatisticManager.isSaved(ele.getTitle() + ele.getPubTime())) {
					counterUniq++;
					if (counterUniq > Config.statistic_uniq_threshold) {
						// 如果超过了3条,那么直接结束本次持久化
						System.out.println("本页插入数据条目------>" + counterInsert);
						return;
					}
					continue;
				}
				counterInsert++;
				ps.setString(1, ele.getTitle());
				ps.setString(2, ele.getPubTime());
				ps.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
				String url = ele.getUrlTaskPojo().getUrl();
				int fromId = searchUrlId(url);
				if (fromId != 0) {
					ps.setInt(4, fromId);
				} else {
					insertUrl(url);
					fromId = searchUrlId(url);
					ps.setInt(4, fromId);
				}
				ps.addBatch();
				// 统计
				StatisticManager.allNumCounter();
				StatisticManager.dayNumCounter();
			}
			ps.executeBatch();
			System.out.println("本页插入数据条目------>" + counterInsert);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBPoolUtils.close(conn, ps);
		}
	}

	/**
	 * 查找对应的url id值
	 * 
	 * @param data
	 * @date 2021年7月20日
	 * @author Antg
	 */
	public static int searchUrlId(String url) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int result = 0;
		try {
			// 获取数据库连接
			conn = DBPoolUtils.getInstance().getBasicDataSource()
					.getConnection();
			// 创建sql语句
			String sql = "select id from news_url where from_url = ?";
			// 获取会话
			ps = conn.prepareStatement(sql);
			ps.setString(1, url);
			rs = ps.executeQuery();
			rs.next();
			result = rs.getInt(1);
		} catch (Exception e) {
		} finally {
			DBPoolUtils.close(conn, ps);
		}
		return result;
	}

	/**
	 * 将一个新的url插入
	 * 
	 * @param url
	 * @date 2021年7月20日
	 * @author Antg
	 */
	public static void insertUrl(String url) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			// 获取数据库连接
			conn = DBPoolUtils.getInstance().getBasicDataSource()
					.getConnection();
			// 创建sql语句
			String sql = "insert into news_url(from_url) values(?)";
			// 获取会话
			ps = conn.prepareStatement(sql);
			ps.setString(1, url);
			ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBPoolUtils.close(conn, ps);
		}
	}
}
